How to Print Multiple Sheets in Excel: 6 Suitable Examples

您所在的位置：网站首页 › print multiple postcards on a single sheet in › How to Print Multiple Sheets in Excel: 6 Suitable Examples

How to Print Multiple Sheets in Excel: 6 Suitable Examples

2024-07-14 09:25| 来源: 网络整理| 查看: 265

Example 1 – Print All Sheets at Once in Excel Case 1.1 – Using the Print Entire Workbook Feature for Printing All Sheets at Once Go to the File tab on the ribbon or press CTRL+P. This will take us to the main menu.

Opening File tab

From the Print option, go to the Settings drop-down menu, select Print Entire Workbook, and click on the Print button.

How to Print Multiple Sheets in Excel

Case 1.2 – Use VBA Code to Print the Entire Workbook Go to the Developer tab on the ribbon. Click on Visual Basic to open the Visual Basic Editor where we will write the VBA codes. Another way to open the Visual Basic Editor is to press ALT + F11.

Opening Visual Basic Editor

Go to Insert and select Module from the drop-down menu.

Inserting Module

Copy and paste the VBA code in Module and click on the RUN button to run the code.

Running the VBA Code to Print Multiple Sheets

VBA Code:

Sub Print_Workbooks() ActiveWorkbook.PrintOut End Sub Case 1.3 – Converting the Excel File into a PDF File with VBA Code to Print All Sheets Go to the Developer tab >> Visual Basic >> Insert >> Module. Copy and paste the VBA code that is shown below.

Converting Excel File into PDF File with VBA Code

VBA Code:

Sub PrintAllSheetToPdf() For Each iSheet In ActiveWorkbook.Worksheets Worksheets(iSheet.Name).Select False Next iSheet With Application.FileDialog(msoFileDialogFolderPicker) .Show iFolder = .SelectedItems(1) & "\" End With iFile = InputBox("Enter New File Name", "PDF File Name") ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=iFolder & iFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True End Sub Press F5 on your keyboard. You will be asked to select the folder where you want to save the PDF file. Select the new file path and click OK.

Choose a Folder to keep the PDF file

A pop-up input box will appear. It will ask you for a name. Write any name for your new PDF file. We named our new PDF file “PrintAllSheet”. Click OK.

Write a name for the PDF File

Each page of the PDF file will carry the data from each of the worksheets from the Excel workbook. Case 1.4 – Print Certain Sheets by Using VBA Code Go to the Developer tab on the ribbon >> click on Visual Basic >> from Insert >> select Module. Insert the following VBA code.

Print Some Certain Sheets by Using VBA Code in Excel

VBA Code:

Sub Print_Certain_Sheets() Worksheets(Array("Apparel-1", "Grocery")).PrintOut End Sub

Apparel-1, and Grocery are the name of the sheets. Run the code by clicking the RUN button. In the end, running the code will print multiple sheets (Apparel-1, and Grocery) at one click.

Example 2 – Print Multiple Active Sheets

We have five sheets of data but there are only three active sheets we want to print.

To select more than one sheet at a time, hold down the Ctrl key then select the sheet names one by one.

Activate more than one sheet at a time

Case 2.1 – Use the Print Active Sheets Feature for Printing Multiple Sheets Press CTRL+P to get the Print menu. From the Print option >> go to the Settings drop-down menu >> select Print Active Sheets >> click Print.

Printing Active Sheets in Excel

Case 2.2 – Use the ActiveSheet Property in VBA Code to Print Only the Active Sheets Go to the Developer tab >> click on Visual Basic >> insert a new Module >> insert the code there.

Use ActiveSheet Property in VBA Code to Print Only the Active Sheets

VBA Code:

Sub Print_ActiveSheets() ActiveSheet.PrintOut End Sub Run the code by pressing the F5 key. This will print all the active sheets on the spreadsheet. Case 2.3 – Using the SelectedSheets Property to Print Selected Excel Sheets Click the Developer tab >> go to Visual Basic >> from the drop-down menu under Insert, go to Module >> insert the following VBA code.

Using SelectedSheets Property to Print Selected Excel Sheets

VBA Code:

Sub Print_SelectedSheets() ActiveWindow.SelectedSheets.PrintOut End Sub Run the code by pressing F5 to get the printed copies of selected sheets. Example 3 – Print Multiple Sheets with a Specific Print Area Case 3.1 – Manually Adjusting the Print Area for Different Sheets Go to the first worksheet >> select the particular area for printing >> go to the Page Layout tab >> click on the Print Area drop-down menu under the Page Setup group >> click on the Set Print Area.

Manually Adjusting the Print Area for Apparel-1 Sheet

Go to other sheets and select the print area >> select Page Layout >> go to Print Area >> press Set Print Area.

Manually Adjusting the Print Area for Grocery Sheet

Press CTRL + P to get the Print menu >> go to the Settings drop-down menu >> select Print Entire Workbook >> click on Print.

Print Entire Workbook with Selected Area

Case 3.2 – Using Print Selection to Print the Same Area of Multiple Sheets Hold down the Ctrl key >> select all the sheet names you want to print >> select the print area.

Print Same Area for Multiple Sheets in Excel

Press CTRL+P to get the Print menu >> go to the Settings drop-down menu >> select Print Selection >> click on Print.

Using Print Selection to Print Same Area of Multiple Sheets

Example 4 – Use the Camera Icon to Print Multiple Excel Sheets on One Page Right-click on the top ribbon to get the Context Menu Bar >> go to Customize the Ribbon.

Customizing the top ribbon

From Choose commands from: >> select Camera >> choose New Tab >> press the Add button >> click on OK.

Adding Camera Icon to the New Tab

You will see the Camera feature under New Tab in the top ribbon.

Go to a sheet >> select the area that you want to print >> go to New Tab >> click Camera.

Using Camera Icon in Excel

Choose a specific sheet where you will bring all the required portions from other sheets >> go to that sheet >> release the mouse cursor there.

Copy Data with Camera Feature in Excel

We have saved all the information in one sheet. Go to Page Layout >> select Print Area >> select Set Print Area.

Setting Print Area

Press CTRL + P to go to the Print menu >> go to Print Settings >> select Print Selection >> choose Landscape Orientation >> press Print.

Finally, you will get all the important parts on one single page.

Print Multiple Excel Sheets on One Page

Example 5 – Using VBA Code to Print Multiple Sheets with Comments in Excel

We’ll print this sheet with the comment.

Excel Sheets having comment

From the Developer tab, click Visual Basic to open the Visual Basic Editor. Go to Insert and select Module from the drop-down menu. Insert this VBA code.

VBA Code to Print Multiple Sheets with Comments

VBA Code:

Sub Print_with_Comnts() Application.DisplayCommentIndicator = xlCommentAndIndicator With ActiveSheet .PageSetup.PrintComments = xlPrintSheetEnd .PrintOut End With End Sub After running the code, you will find the comment at the end of the sheet.

Printed Copy

Example 6 – Print Multiple Hidden Worksheets

We hid a few sheets.

Some Excel Sheets are Hidden

Click on the Developer tab and select Visual Basic. Select Module from the drop-down menu under Insert. Copy and paste the VBA code below to see the final output.

VBA Code to Print All Sheets Including Hidden Ones

VBA Code:

Sub Print_Hidden_sheets() Dim Vis As Long Dim sheets As Worksheet For Each sheets In ActiveWorkbook.Worksheets With sheets Vis = .Visible .Visible = xlSheetVisible .PrintOut .Visible = Vis End With Next sheets End Sub After running the code, you will get all sheets printed, including the hidden ones.

All printed sheets including the hidden ones

How to Print an Excel Sheet with Row and Column Indexes From Page Layout, click on the Drop-Down Arrow for Page Setup.

Using Page Setup Option

Go to the Sheet tab. Check Rows and columns headings. Press OK.

Print an Excel Sheet with Row and Column Index

You will get the row and column index in that printed sheet.

How to Print an Excel Sheet with Row and Column Numbers

Frequently Asked Questions

How to print an Excel sheet in A4 size full page?

From Page Layout, go to the Drop-Down Arrow in the Page Setup group. In the Paper Size box, select A4, then press OK.

How to print an Excel sheet on a full page?

From Page Layout, go to the Drop-Down Arrow beside the Page Setup group. From the Scaling section, check Fit to box and write 1 in page wide by and 1 in tall, then press OK.

Download the Practice Workbook

Printing Multiple Sheets.xlsm

Sabrina Ayon, a Computer Science and Engineering graduate from United International University, has been an integral part of the ExcelDemy project for two years. She authored 150+ articles, excelling in instructing through visually engaging Excel tutorials. With a passion for teaching, Sabrina conducted sessions on Excel VBA, sharing her knowledge and insights with others. Currently holding the position of Project Manager for the ExcelDemy Visual Development Project, she oversees various aspects of the project, ensuring its smooth operation... Read Full Bio

We will be happy to hear your thoughts Leave a reply Cancel reply

【本文地址】

How to Print Multiple Sheets in Excel: 6 Suitable Examples

How to Print Multiple Sheets in Excel: 6 Suitable Examples

今日新闻

推荐新闻